﻿using INTEX.DataBase;
using INTEX.DataBase.Tools;
using ReflectionStudio.Core;
using ReflectionStudio.Core.Events;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.IO;
using System.Linq;
using System.Windows;
namespace WPFExcelReport
{
    class JLTWB18TOTReport : BaseReport
    {
        #region ----------------PROPERTIES----------------------------------
        public OracleConnection OracleDB;
        public OracleCommand orlReadCommand = new OracleCommand();
        public IntexReptServBLClient proxy;
        private Dictionary<string, object> dicParamater;
        public DataSet dsResult;
        public DataSet setUI = new DataSet();
        string ABPAUFFKRS;
        string ABPAUFFLFD;
        string ABPAUFFPOS;
        string DocType;
        string CellName = "";
        string CellValue = "";
        public string LANGUAGE = "",ART="",COLOR="",PVH = "";
        string strCompany;
        string strExcelFile;
        Dictionary<string, int> qpplan = new Dictionary<string, int>();
        
        #endregion
        public JLTWB18TOTReport()
            : base()
        {
        }

        public JLTWB18TOTReport(Dictionary<string, object> dicParamater)
            : base(dicParamater)
        {
            strCompany = dicParamater["Company"].ToString().Trim();
            strExcelFile = dicParamater["ExcelFile"].ToString().Trim();
            if (dicParamater.ContainsKey("ROOTDRIVE"))
            {
                Root = dicParamater["ROOTDRIVE"].ToString();
            }
            else
            {
                Root = "C:\\IntexReport";
            }
                      						

        } 
        #region------------------------------Function------------------------
        /*ART	COLOR	WBBPACKNR	WIDTH	WBFKNEUDAT	WBFKWARSCH	WBFKWMPOS	MTR	YARD	TOTRATE	YRATE	
                    BEM	WBFFSCHL DES	RATE	TYPE	FDEFECT	EDEFECT*/
        public void FillInExcelData(DataSet setSoruce, int xStart = 2, int yStart = 1)
        {
            try
            {

                /* VKPART	VKPFARBE	VKPMEEH	RATE	KDART	DES	MTR	YARD	ROLLNUM	QTY	BQTY	BROLLNUM	AVGRATE	
                MINRATE	MAXRATE	ROLLNUM1	ROLLQTY1	ROLLNUM2	ROLLQTY2	ROLLNUM3	ROLLQTY3	
                   ROLLNUM4	ROLLQTY4	ROLLNUM5	ROLLQTY5
                 */
                DataTable dtSoruce = setSoruce.Tables["WB18TOT"];  
                DataRow temp = dtSoruce.Rows[0];
                string VKPART = temp["VKPART"].ToString().Trim();
                exAgent.SetCellValue(3, 8, VKPART);
                string VKPFARBE = temp["VKPFARBE"].ToString().Trim();                
                exAgent.SetCellValue( 3, 25 , VKPFARBE);
                string KDART = temp["KDART"].ToString().Trim();
                exAgent.SetCellValue( 4, 8 , KDART);
                string DES = temp["DES"].ToString().Trim();
                exAgent.SetCellValue(5, 8, DES);
                string REFERANCE = temp["REFERANCE"].ToString().Trim();
                if (LANGUAGE == "V")
                {
                    exAgent.SetCellValue(5, 25, REFERANCE);
                    if(PVH=="J")
                    {
                        exAgent.SetCellValue(6, 29, "扣分数/百码");
                        exAgent.SetCellValue(2891, 1, "百码平均分：");
                        exAgent.SetCellValue(2891, 24, "百码最高分：");
                        exAgent.SetCellValue(2892, 1, "百码最低分：");
                    }
                    else
                    {
                        exAgent.SetCellValue(6, 29, "扣分数/百平方码");
                        exAgent.SetCellValue(2891, 1, "百平方码平均分：");
                        exAgent.SetCellValue(2891, 24, "百平方码最高分：");
                        exAgent.SetCellValue(2892, 1, "百平方码最低分：");
                    }
                }
                else
                {
                    if (PVH == "J")
                    {
                        exAgent.SetCellValue(5, 25, "American 4 point inspection system.Average:   /100yd、Single:   /100yd");
                        exAgent.SetCellValue(6, 29, "Points/100yd");
                        exAgent.SetCellValue(2891, 1, "Average Defective Points Per 100yd");
                        exAgent.SetCellValue(2891, 24, "Max. defective Points Per 100yd");
                        exAgent.SetCellValue(2892, 1, "Min. Defective Points Per 100yd");
                        exAgent.SetCellValue(2893, 1, "Number of Rolls having 0-10 points Per 100yd");
                        exAgent.SetCellValue(2893, 24, "Number of Rolls having 11-15 points Per 100yd");
                        exAgent.SetCellValue(2894, 1, "Number of Rolls having 16-20 points Per 100yd");
                        exAgent.SetCellValue(2894, 24, "Number of Rolls having 21-30 points Per 100yd");
                        exAgent.SetCellValue(2895, 1, "Number of Rolls >30 points Per 100yd");
                    }
                    else
                    {
                        exAgent.SetCellValue(5, 25, "American 4 point inspection system.Average:   /100sq.yd、Single:   /100sq.yd");
                        exAgent.SetCellValue(6, 29, "Points/100sq.yd");
                        exAgent.SetCellValue(2891, 1, "Average Defective Points Per 100sq.yd");
                        exAgent.SetCellValue(2891, 24, "Max. defective Points Per 100sq.yd");
                        exAgent.SetCellValue(2892, 1, "Min. Defective Points Per 100sq.yd");
                        exAgent.SetCellValue(2893, 1, "Number of Rolls having 0-10 points Per 100sq.yd");
                        exAgent.SetCellValue(2893, 24, "Number of Rolls having 11-15 points Per 100sq.yd");
                        exAgent.SetCellValue(2894, 1, "Number of Rolls having 16-20 points Per 100sq.yd");
                        exAgent.SetCellValue(2894, 24, "Number of Rolls having 21-30 points Per 100sq.yd");
                        exAgent.SetCellValue(2895, 1, "Number of Rolls >30 points Per 100sq.yd");
                    }
                }
                
                string VKPMEEH = temp["VKPMEEH"].ToString().Trim();
                if (VKPMEEH=="M  ")
                {
                    if(LANGUAGE=="V")
                    {
                        /*if (PVH == "J")
                        {
                            exAgent.SetCellValue(6, 29, "扣分数/百米");
                            exAgent.SetCellValue(2891, 1, "百米平均分：");
                            exAgent.SetCellValue(2891, 24, "百米最高分：");
                            exAgent.SetCellValue(2892, 1, "百米最低分：");
                        }
                        else
                        {
                            exAgent.SetCellValue(6, 29, "扣分数/百平方米");
                            exAgent.SetCellValue(2891, 1, "百平方米平均分：");
                            exAgent.SetCellValue(2891, 24, "百平方米最高分：");
                            exAgent.SetCellValue(2892, 1, "百平方米最低分：");
                        }
                        */
                        exAgent.SetCellValue(2890, 10, "总米长：");
                        exAgent.SetCellValue(2890, 29, "二等品总米长：");
                        exAgent.SetCellValue(2893, 1, "0-10分卷数/米数：");
                        exAgent.SetCellValue(2893, 24, "11-15分卷数/米数：");
                        exAgent.SetCellValue(2894, 1, "16-20分卷数/米数：");
                        exAgent.SetCellValue(2894, 24, "21-30分卷数/米数：");
                        exAgent.SetCellValue(2895, 1, "30分以上卷数/米数：");
                    }
                    if(LANGUAGE=="E")
                    {
                        /*if(PVH=="J")
                        {
                            
                            exAgent.SetCellValue(2893, 1, "Number of Rolls having 0-10 points Per 100m");
                            exAgent.SetCellValue(2893, 24, "Number of Rolls having 11-15 points Per 100m");
                            exAgent.SetCellValue(2894, 1, "Number of Rolls having 16-20 points Per 100m");
                            exAgent.SetCellValue(2894, 24, "Number of Rolls having 21-30 points Per 100m");
                            exAgent.SetCellValue(2895, 1, "Number of Rolls >30 points Per 100m");
                            //exAgent.SetCellValue(5, 25, "American 4 point inspection system.Average:   /100m、Single:   /100m");
                        }
                        else
                        {
                            
                            exAgent.SetCellValue(2893, 1, "Number of Rolls having 0-10 points Per 100sq.m");
                            exAgent.SetCellValue(2893, 24, "Number of Rolls having 11-15 points Per 100sq.m");
                            exAgent.SetCellValue(2894, 1, "Number of Rolls having 16-20 points Per 100sq.m");
                            exAgent.SetCellValue(2894, 24, "Number of Rolls having 21-30 points Per 100sq.m");
                            exAgent.SetCellValue(2895, 1, "Number of Rolls >30 points Per 100sq.m");
                            //exAgent.SetCellValue(5, 25, "American 4 point inspection system.Average:   /100sq.m、Single:   /100sq.m");
                        }*/
                        
                        exAgent.SetCellValue( 2890, 10 , "Total Meter");
                        exAgent.SetCellValue( 2890, 29 , "Total Second quality Meter");
                    }
                }
                //string MTR = temp["MTR"].ToString().Trim();
                //string YARD = temp["YARD"].ToString().Trim();
                string ROLLNUM = temp["ROLLNUM"].ToString().Trim();
                exAgent.SetCellValue( 2890, 7 , ROLLNUM);
                string QTY = temp["QTY"].ToString().Trim();
                exAgent.SetCellValue( 2890, 16 , QTY);
                string BQTY = temp["BQTY"].ToString().Trim();
                exAgent.SetCellValue( 2890, 36 , BQTY);
                string BROLLNUM = temp["BROLLNUM"].ToString().Trim();
                exAgent.SetCellValue( 2890, 26 , BROLLNUM);
                string AVGRATE = temp["AVGRATE"].ToString().Trim();
                exAgent.SetCellValue( 2891, 19 , AVGRATE);
                string MINRATE = temp["MINRATE"].ToString().Trim();
                exAgent.SetCellValue( 2891, 36 , MINRATE);
                string MAXRATE = temp["MAXRATE"].ToString().Trim();
                exAgent.SetCellValue( 2892, 19 , MAXRATE);
                string ROLLNUM1 = temp["ROLLNUM1"].ToString().Trim();
                string ROLLQTY1 = temp["ROLLQTY1"].ToString().Trim();                
                exAgent.SetCellValue( 2893, 19 , ROLLNUM1+"/"+ROLLQTY1);
                string ROLLNUM2 = temp["ROLLNUM2"].ToString().Trim();
                string ROLLQTY2 = temp["ROLLQTY2"].ToString().Trim();
                exAgent.SetCellValue( 2893, 36 , ROLLNUM2+"/"+ROLLQTY2);
                string ROLLNUM3 = temp["ROLLNUM3"].ToString().Trim();
                string ROLLQTY3 = temp["ROLLQTY3"].ToString().Trim();
                exAgent.SetCellValue( 2894, 19 , ROLLNUM3+"/"+ROLLQTY3);
                string ROLLNUM4 = temp["ROLLNUM4"].ToString().Trim();
                string ROLLQTY4 = temp["ROLLQTY4"].ToString().Trim();
                exAgent.SetCellValue( 2894, 36 , ROLLNUM4+"/"+ROLLQTY4);
                string ROLLNUM5 = temp["ROLLNUM5"].ToString().Trim();
                string ROLLQTY5 = temp["ROLLQTY5"].ToString().Trim();
                exAgent.SetCellValue( 2895, 19 , ROLLNUM5+"/"+ROLLQTY5);

                //DataTable dtrSoruce = setSoruce.Tables["WB18"];
                //int i = 7;
                //foreach (DataRow dw in dtrSoruce.Rows)
                /*{WBBEINDAT	ROLL	WBBSTKNR	MTR	YARD	WBBTAX	WBBDPART	
                   COL	HAND	SKEW	BOW	DQ	RATE	WIDTH	AVGWID	AQTY	BQTY	DEFECT*/
                   /* string WBBEINDAT = dw["WBBEINDAT"].ToString().Trim();
                    exAgent.SetCellValue(i, 1, WBBEINDAT);
                    string ROLL = dw["ROLL"].ToString().Trim();
                    exAgent.SetCellValue(i, 4, ROLL);
                    string MTR = dw["MTR"].ToString().Trim();
                    exAgent.SetCellValue(i, 6, MTR);
                    string YARD = dw["YARD"].ToString().Trim();
                    exAgent.SetCellValue(i, 8, YARD);
                    string WBBTAX = dw["WBBTAX"].ToString().Trim();
                    exAgent.SetCellValue(i, 10, WBBTAX);
                    string WBBDPART = dw["WBBDPART"].ToString().Trim();
                    exAgent.SetCellValue(i, 12, WBBDPART);
                    string COL = dw["COL"].ToString().Trim();
                    exAgent.SetCellValue(i, 14, COL);
                    string HAND = dw["HAND"].ToString().Trim();
                    exAgent.SetCellValue(i, 16, HAND);
                    string WIDTH = dw["WIDTH"].ToString().Trim();
                    exAgent.SetCellValue(i, 18, WIDTH);
                    string SKEW = dw["SKEW"].ToString().Trim();
                    exAgent.SetCellValue(i, 22, SKEW);
                    string BOW = dw["BOW"].ToString().Trim();
                    exAgent.SetCellValue(i, 24, BOW);
                    string DQ = dw["DQ"].ToString().Trim();
                    exAgent.SetCellValue(i, 26, DQ);
                    string RATE = dw["RATE"].ToString().Trim();
                    exAgent.SetCellValue(i, 28, RATE);
                    string AQTY = dw["AQTY"].ToString().Trim();
                    exAgent.SetCellValue(i, 31, AQTY);
                    string BQTY1 = dw["BQTY"].ToString().Trim();
                    exAgent.SetCellValue(i, 33, BQTY1);
                    string DEFECT = dw["DEFECT"].ToString().Trim();
                    exAgent.SetCellValue(i, 35, DEFECT);
                    i = i + 1;
                } */

                DataTable dtrSoruce = setSoruce.Tables["WB18"];
                int i = setSoruce.Tables["WB18"].Rows.Count;
                DataTable dtOutput = new DataTable();
                dtOutput.Columns.Add("WBBEINDAT", typeof(System.String));
                //dtOutput.Columns.Add("X20", typeof(System.String));
                dtOutput.Columns.Add("X1", typeof(System.String));
                dtOutput.Columns.Add("X2", typeof(System.String));
                dtOutput.Columns.Add("ROLL", typeof(System.String));
                dtOutput.Columns.Add("X3", typeof(System.String));
                dtOutput.Columns.Add("MTR", typeof(System.String));
                dtOutput.Columns.Add("X4", typeof(System.String));
                dtOutput.Columns.Add("YARD", typeof(System.String));
                dtOutput.Columns.Add("X5", typeof(System.String));
                dtOutput.Columns.Add("WBBTAX", typeof(System.String));
                dtOutput.Columns.Add("X6", typeof(System.String));
                dtOutput.Columns.Add("WBBDPART", typeof(System.String));
                dtOutput.Columns.Add("X7", typeof(System.String));
                dtOutput.Columns.Add("COL1", typeof(System.String));
                dtOutput.Columns.Add("COL2", typeof(System.String));
                dtOutput.Columns.Add("COL3", typeof(System.String));
                dtOutput.Columns.Add("HAND", typeof(System.String));
                dtOutput.Columns.Add("X9", typeof(System.String));
                dtOutput.Columns.Add("WIDTHMAX", typeof(System.String));
                dtOutput.Columns.Add("X10", typeof(System.String));
                dtOutput.Columns.Add("WIDTHMIN", typeof(System.String));
                dtOutput.Columns.Add("X12", typeof(System.String));
                dtOutput.Columns.Add("SKEWMAX", typeof(System.String));
                dtOutput.Columns.Add("SKEWMIN", typeof(System.String));
                dtOutput.Columns.Add("BOWMAX", typeof(System.String));
                dtOutput.Columns.Add("BOWMIN", typeof(System.String));
                dtOutput.Columns.Add("DQ", typeof(System.String));
                dtOutput.Columns.Add("X15", typeof(System.String));
                dtOutput.Columns.Add("RATE", typeof(System.String));
                dtOutput.Columns.Add("X16", typeof(System.String));
                dtOutput.Columns.Add("X17", typeof(System.String));
                dtOutput.Columns.Add("AQTY", typeof(System.String));
                dtOutput.Columns.Add("X18", typeof(System.String));
                dtOutput.Columns.Add("BQTY", typeof(System.String));
                dtOutput.Columns.Add("X19", typeof(System.String));
                dtOutput.Columns.Add("DEFECT", typeof(System.String));
                dtOutput.Merge(dtrSoruce, false, MissingSchemaAction.Ignore);

                exAgent.DataTableToExcel(dtOutput, 7, 1);

                int count = dtOutput.Rows.Count;
                exAgent.SetCellValue(1, 1, 1);
                //exAgent.BatchConvert2Number(7, 6, count + 6, 6);
                //exAgent.BatchConvert2Number(7, 8, count + 6, 8);
                //exAgent.BatchConvert2Number(7, 27, count + 6, 27);
                //exAgent.BatchConvert2Number(7, 29, count + 6, 29);
                if(LANGUAGE=="V")
                {
                    exAgent.SetCellValue(1, 1, "金陵布面分析报告");
                }
                if(LANGUAGE=="E")
                {
                    exAgent.SetCellValue(1, 1, "Jinling Fabric Performance Report");

                }
                exAgent.HideRows(i+7,1,2889,1);
                						
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }        
        public override void WriteExcelReport(string fileType)
        {
            bool IsFTP = false;
            try
            {
                strCompany = "JLT";
                strExcelFile = @"JLT\JLTQUALITY.xls";
                if(LANGUAGE =="V")
                {
                    Standard = "WB18TOTV";
                }
                if (LANGUAGE == "E")
                {
                    Standard = "WB18TOTE";
                }
                TemplatePath = Path.Combine(PathHelper.ApplicationPath, @"Report\JLT\JLTQUALITY.xls");
                destfolder = SetFolder(Root, "成品检验");
                string filedate = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString();
                string filename = ART + "-" + COLOR;
                string newfilename = "";
                if (LANGUAGE == "V")
                {
                    newfilename = @"成品质量报告" + filename;
                } if (LANGUAGE == "E")
                {
                    newfilename = @"FabricPerformanceReport" + filename;
                }
                
                exAgent.CheckWorkBook(newfilename);
                ServerShareFile("", year, month, newfilename, fileType);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }
        private void ServerShareFile(string KRS, string year, string month, string ordernumer, string fileType)
        {
            string destfile, tempfile = "";
            string fileabsname = ordernumer;
            try
            {
                filename = destfolder + "\\" + fileabsname + ".xls"; ;
                destfile = filename;
                tempfile = CreateFile(ordernumer);
                if (File.Exists(destfile))
                {
                    File.Delete(destfile);
                }
                File.Move(tempfile, destfile);

                if (fileType == "OpenExcel")
                {
                    System.Diagnostics.Process.Start("EXCEL.EXE", filename);
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            finally
            {
                if (File.Exists(tempfile))
                {
                    File.Delete(tempfile);
                }
            }
        }
        private string CreateFile(string orderno)
        {
            string filename = "";
            try
            {
                string pwd = "";
                string sheetName = orderno;
                exAgent.Open(TemplatePath);
                exAgent.Company = strCompany;
                exAgent.strExcelFile = strExcelFile;
                exAgent.CreateNewSheet(orderno + ".xls", Standard, sheetName, pwd);
                filename = exAgent.FileName;
                exAgent.SetCurrentSheet(1);
                FillInExcelData(dsSource, 3, 1);
                exAgent.Save();
                exAgent.Close();
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return filename;
        }
        #endregion
    }
}